package com.topscomm.meet.utils;

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class GenerateExcelTemplateUtil extends  BaseTemplateUtil{

	public static void convertToPdf(String srcExcelPath, String destPdfPath)throws IOException {
		if (!getExcelLicense()) { // 验证License 若不验证则转化出的pdf文档会有水印产生
	        return;
        }
		try (FileOutputStream fileOS  = new FileOutputStream(destPdfPath)) {
	        Workbook workBook = new Workbook();
	      //Load a file
	        workBook.loadFromFile(srcExcelPath);
            //Fit to page
	        workBook.getConverterSetting().setSheetFitToPage(true);
			//获取第一个工作表
			Worksheet worksheet = workBook.getWorksheets().get(0);
            //Save to PDF file
	        workBook.saveToFile(destPdfPath, FileFormat.PDF);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void readWordFile(Map paraMap, String sourceFilePath, String destFilePath, String fileName) throws Exception {
		File demoFile = new File(sourceFilePath);
		FileInputStream in = new FileInputStream(demoFile);
		XSSFWorkbook document = new XSSFWorkbook(in);
		replaceExcelText(document, paraMap);



		OutputStream os = new FileOutputStream(destFilePath + "/" + fileName + ".xlsx");
		document.write(os);
		closeStream(in);
		closeStream(os);
	}

	private static void replaceExcelText(XSSFWorkbook document, Map<String, Object > paraMap) {
		XSSFSheet sheet0 = document.getSheetAt(0);
		if (sheet0 == null) {
			return;
		}
		List<Map<String, Object>> list = (List<Map<String, Object>>) paraMap.get("productList");
		//获取最后一行的num，即总行数。此处从0开始计数
        int maxRow = sheet0.getLastRowNum();
        for (int row = 0; row <= maxRow; row++) {
        	String stringCellValue = sheet0.getRow(row).getCell(0).getStringCellValue();
        	if (stringCellValue.indexOf("${for:") != -1) {
        		int i = 0;
        		if (CollectionUtils.isEmpty(list)) {
					list = new ArrayList<>();
					Map<String, Object> item = new HashMap<>();
					list.add(item);
				}
				for( ; i<list.size() - 1; i++) {
					copyNewRow(sheet0, row + i, row + i);
					replaceRowValue(list.get(i), sheet0, row + i, true);
				}
				replaceRowValue(list.get(i), sheet0, row + i, true);
				// 跳过增加的list行内容
				row += i;
				maxRow += i;
        	}
        	else {
	            replaceRowValue(paraMap, sheet0, row, false);
            }
        }
	}

	private static void replaceRowValue(Map<String, Object> paraMap, XSSFSheet sheet0, int row, boolean dealForItem) {
		int maxRol = sheet0.getRow(row).getLastCellNum();
		for (int rol = 0; rol < maxRol; rol++){
			XSSFCell cell = sheet0.getRow(row).getCell(rol);
			replaceCellValue(cell, paraMap, dealForItem);
		}
	}

	private static void copyNewRow(XSSFSheet sheet, int position, int srcRow) {
		XSSFRow row = sheet.getRow(srcRow);
		int lastCellNum = row.getLastCellNum();
		sheet.shiftRows(position, sheet.getLastRowNum(), 1, true, true); //10-最后一行，向下移动一行,保持原来的宽度
		Row newRow = sheet.createRow(position);
		newRow.setRowStyle(row.getRowStyle());
		for(int i = 0; i < lastCellNum; i++) {
			Cell newCell = newRow.createCell(i);
			String stringCellValue = row.getCell(i).getStringCellValue();
			newCell.setCellStyle(row.getCell(i).getCellStyle());
			newCell.setCellValue(stringCellValue);
		}
	}

	/**
	 *
	 * @param cell
	 * @param paraMap
	 * @param dealForItem 是否处理for循环的标签${for: price}, true处理，false跳过
	 */
	private static void replaceCellValue(XSSFCell cell, Map<String, Object > paraMap, boolean dealForItem) {
		String stringCellValue = cell.getStringCellValue();
		int middleIndex = 0;
		if (stringCellValue.indexOf("${for:") != -1) {
			if (!dealForItem) {
				return;
			}
			middleIndex = stringCellValue.indexOf(":");
		}
		Matcher match = regExMatch(stringCellValue);
		if (match.find()) {
			String result = "";
			int startIndex = stringCellValue.indexOf("$");
			middleIndex = middleIndex == 0 ? stringCellValue.indexOf("{") : middleIndex + 1;
			int endIndex = stringCellValue.indexOf("}");
			int length = stringCellValue.length();
			if (startIndex != 0 && endIndex != length) {//该单元格不是以$开头和不以}结尾
				String key = stringCellValue.substring(middleIndex + 1, endIndex);
				String valueBykey = getValueBykey(key, paraMap);
				result = stringCellValue.substring(0, startIndex) + valueBykey + stringCellValue.substring(endIndex + 1);
				cell.setCellValue(result);
			}
			else if(startIndex != 0 && endIndex == length) {//该单元格不是以$开头和以}结尾
				String key = stringCellValue.substring(middleIndex + 1, endIndex);
				String valueBykey = getValueBykey(key, paraMap);
				result = stringCellValue.substring(0, startIndex) + valueBykey;
				cell.setCellValue(result);
			}
			else if(startIndex == 0 && endIndex != length) {//该单元格以$开头和不以}结尾
				String key = stringCellValue.substring(middleIndex + 1, endIndex);
				String valueBykey = getValueBykey(key, paraMap);
				result = valueBykey + stringCellValue.substring(endIndex + 1);
				cell.setCellValue(result);
			}
			else {
				String key = stringCellValue.substring(middleIndex + 1, endIndex);
				String valueBykey = getValueBykey(key, paraMap);
				cell.setCellValue(valueBykey);
			}
			replaceCellValue(cell, paraMap, dealForItem);

		}
	}

	private static Matcher regExMatch(String content) {
		String regEx = "\\$\\{.+?\\}";
   	 	Pattern pattern = Pattern.compile(regEx);
        return pattern.matcher(content);
	}

	/**
	 * 行复制功能
	 * @param
	 * @param
	 */
//	public static void copyRow(HSSFWorkbook wb,HSSFRow fromRow,HSSFRow toRow,boolean copyValueFlag){
//		for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
//			HSSFCell tmpCell = (HSSFCell) cellIt.next();
//			HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
//			copyCell(wb,tmpCell, newCell, copyValueFlag);
//		}
//	}


    private static String getValueBykey(String key, Map<String, Object> map) {
        String returnValue="";
        if (key != null) {
            try {
                returnValue=map.get(key)!=null ? map.get(key).toString() : "";
            } catch (Exception e) {
                // TODO: handle exception
                System.out.println("key:"+key+"***"+e);
                returnValue="";
            }

        }
        return returnValue;
    }

   /**
    * 关闭输入流
    * @param is
    */
   private static void closeStream(InputStream is) {
	   if (is != null) {
	         try {
	            is.close();
	         } catch (IOException e) {
	            e.printStackTrace();
	         }
	      }
   }

   /**
    * 关闭输出流
    * @param is
    */
   private static void closeStream(OutputStream is) {
	   if (is != null) {
	         try {
	            is.close();
	         } catch (IOException e) {
	            e.printStackTrace();
	         }
	      }
   }

}
